package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import Model.Client;


public class DBClient implements IFDBClient {
	private static Connection con;
	Statement stmt;
	public DBClient() 
	{
		con = DBCon.getInstance().getDBCon();
	}

	@Override
	public int createClient(String ssn,String fname,String lname,String address,int phone,int mID, int Session)
	{ int cID =-1;
		try{
			 cID=GetMax.getMaxId("Select max(cID) from Client")+1;//Gets the maximum cID from the DB and adds+1 for the next ID
			String query ="INSERT INTO Client(cID, ssn,fname, lname, address, phone, mID, session)  VALUES('"+cID+"','"+ssn+"','"+fname+"','"+lname+"','"+address+"','"+phone+"','"+mID+"','"+Session+"')";
			 Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
			}
		return cID;
	}

	@Override
	public boolean deleteClient(int cID)
	{	boolean y= false; 
		try{
			String query="DELETE FROM Client WHERE cID = '"+cID+"'";
			Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
	          y = true;
		}
		catch(Exception e)
		{e.printStackTrace();
		y=false;
		}
		return y;
	}

	@Override
	public boolean updateClient(Client g, int choice)
	{  Client suObj = g;
		boolean exec =false;
	
		String query="";
            
          	switch(choice){
        	case 0: //Update First Name
        		query="UPDATE Client SET "+
        			 	  "ssn ='"+ suObj.getSsn()+"'"+
        			 	  " WHERE cID = '"+ suObj.getcID() + "'";
        			 	 exec=true;
        			 	 break;
        	case 1: //Update First Name
        		query="UPDATE Client SET "+
        			 	  "fname ='"+ suObj.getFname()+"'"+
        			 	  " WHERE cID = '"+ suObj.getcID() + "'";
        			 	 exec =true;
        			 	 break;
        	case 2://Update Last Name
        		query="UPDATE Client SET "+
        				"lname ='"+ suObj.getLname()+"'"+
        			 	 " WHERE cID = '"+ suObj.getcID() + "'";
        			 	 exec =true;
        			 	 break;
        	case 3://Update Address
        		query="UPDATE Client SET "+
        				"address ='"+ suObj.getAddress()+"'"+
        			 	 " WHERE cID = '"+ suObj.getcID() + "'";
        			 	 exec =true;
        			 	 break;
        	case 4://Update Phone
        		query="UPDATE Client SET "+
        				"phone ='"+ suObj.getPhone()+"'"+
        			 	 " WHERE cID = '"+ suObj.getcID() + "'";
        			 	 exec =true;
        			 	 break;
        	case 5://Update Subscription
        		query="UPDATE Client SET "+
        				"mID ='"+ suObj.getmID()+"'"+
        			 	 " WHERE cID = '"+ suObj.getcID() + "'";
        			exec=true;
        		break;
        	
        	case 6://Update Session
        		query="UPDATE Client SET "+
        				"session = '"+suObj.getSession()+"'"+
        				"WHERE cID ='"+suObj.getcID()+"'";
        		exec = true;
        	case 7://Update All
        		query="UPDATE Client SET "+
        				"ssn ='"+ suObj.getSsn()+"', "+
        			 	  "fname ='"+ suObj.getFname()+"', "+
        			 	  "lname ='"+ suObj.getLname() + "'," +
        			 	 "address ='"+ suObj.getAddress() + "'," +
        			 	"phone ='"+ suObj.getPhone() + "'," +
        			 	"mID ='"+ suObj.getmID() + "'," +
        			 	"session ='"+ suObj.getSession() + "'" +
        			          " WHERE cID = '"+ suObj.getcID() + "'";
        		exec=true;
        		break;}
          	
              
                try{
            	  Statement stmt = con.createStatement();
            	  stmt.setQueryTimeout(5);
            	  stmt.executeUpdate(query);
            	  stmt.close();
            	  
              }
              catch(Exception e)
              {e.printStackTrace();}
             
			return exec;
          	}
	
	
	private String buildQuery()
	{
	    String query="SELECT cID, ssn, fname, lname, address, phone, mID, session  FROM Client";
		/*
		if (wClause.length()>0)
			query=query+" WHERE ";
			*/
		return query;
	}
	
	@Override
	public ArrayList<Client> miscWhere()
	{
            ResultSet results;
	    ArrayList<Client> list = new ArrayList<Client>();	
		
	    String query =  buildQuery();
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Client cObj = new Client();
		         cObj = buildClient(results);	
                 list.add(cObj);	
		}//end while
                 stmt.close();     
                 
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	
	@Override
	public Client findClient(int cID)
	{
		ResultSet results;
		Client cObj = new Client();
                
	        String query =  buildQuery() + " WHERE cID ="+"'"+cID+"'";
                System.out.println(query);
		try{ // 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            cObj = buildClient(results);
                            
                            stmt.close();
                            
                            
			}
                        else{ //no Client was found
                            cObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return cObj;
	}
	private Client buildClient(ResultSet results)
    {   Client suObj = new Client();
        try{ // the columns from the table glayee  are used
              suObj.setcID(results.getInt("cID"));
              suObj.setSsn(results.getString("ssn"));
              suObj.setFname(results.getString("fname"));
              suObj.setLname(results.getString("lname"));
              suObj.setAddress(results.getString("address"));
              suObj.setPhone(results.getInt("phone"));
              suObj.setmID(results.getInt("mID"));
              suObj.setSession(results.getInt("Session"));

              
        }
       catch(Exception e)
       {
           System.out.println("error in building the Client object");
       }
       return suObj;
}

	@Override
	public Client findClientBySSN(String ssn) {
		ResultSet results;
		Client cObj = new Client();
                
	        String query =  buildQuery() + " WHERE ssn ="+"'"+ssn+"'";
                System.out.println(query);
		try{ // 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            cObj = buildClient(results);
                            
                            stmt.close();
                            
                            
			}
                        else{ //no Client was found
                            cObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return cObj;
	}
	
}
